In this spreadsheet we use data for the Dow Jones Industrials to understand certain fundamental market relationships. With the data, in columns A through F, you can create some formulas and observe a number of characteristics of the Dow Jones Average. These long term historical characteristics provide a means of judging the market's current position.
This spreadsheet uses some information about the Dow Jones Industrials that appears regularly in Barron's. Although this table is printed with some regularity, there is no set schedule, so you will have to watch for it to appear. Since most of the data is annual, there is no need to perform frequent updating. Data for the previous calendar year is usually available by May.
The basic data is reproduces in columns A through F and examples of possible calculations are given in columns G through M. For comparative purposes, some historical averages and standard deviations are calculated near the bottom of the worksheet in rows 71 and 72.
In the spreadsheet, the return on equity (column G) is current earnings per share divided by book value.
Earnings Per Share (column D)
ROE = --------------------
Book Value (column C)
The price-earnings ratio (column H) is a trailing ratio because it takes the index value divided by the last 12 months' earnings per share. For comparative purposes, we have also computed a normalized P/E (column I), which is the current index value divided by the following year's expected earnings.
Closing Price (column B)
Trailing P/E = --------------------
Earnings Per Share (column D)
Closing Price (column B)
Normalized P/E = --------------------
Earnings Per Share (column D)
(Following Year)
In some years, the calculated price-earnings ratios could be either unusually large or negative. For example, in 1932, the depths of the Depression, total earnings for the industrials were - $0.51. Using this figure would result in a trailing P/E of -117.5:
59.93
-----_ = -117.5
-$0.51
Clearly, such a number is not meaningful, and negative P/Es are reported as such in the financial media. As another example, in 1982 earnings were $9.15 and the Dow closed at 1046.54, which gives a trailing P/E of 114.4. Again, this P/E is an aberration--what a statistician would call an outlier.
The problem for an investor is what to do about these numbers. If you were to calculate an average P/E, the average would be distorted by including these outliers. Since you are interested in learning something about long-run performance, determining an average value and the variation around that value is critical. As a reasonable rule of thumb, we will simply exclude for any calculation P/E ratios that are negative or greater than 30. Essentially this excludes the aberrations caused by the Depression (1932 and 1933) and the more recent sharp recession in 1982.
To exclude these numbers, we use the database average function, @DAVG. This function allows us to determine an average of those values that satisfy the conditions, or criteria, we specify. The form of the function is:
@DAVG(input range, offset, criterion range)
where: input range is the area of the spreadsheet where
the data is located, for trailing P/E, cells H7 to H67;
offset counts the number of columns from the beginning of
the database, starting with zero. Since we have only one
column of data, the offset in this case is zero.
specify our conditions. In this case, we have our criteria
immediately under the data we are analyzing, in cells H69
and H70 for trailing P/E.
For our trailing price-earnings ratios, the formula is:
@DAVG(H7..H67,0,H69..H70)
Certain quirks of 1-2-3 must be recognized when using this database function. First, the input range must include the identifier of the data field we are analyzing. Second, the function does not work correctly of there is a blank cell or label between the field name and the first item of data. (Don't ask me why; I have no idea! It's silly and often causes trouble.) This second reason is why we have an entry in the row for the year 1928, even though there is no data for that year other than the closing value of the Dow. Third, it makes a difference whether you use absolute or relative addresses in the criterion. All criteria should be written with relative addresses.
Finally, we have to specify our criteria. There are a number of ways to do this. We will make a single formula, using the logical AND. For example, the average of P/Es that are positive and less than or equal to 30 in our criterion would be +H8>0#AND#+H8<=30. For other ways to set this up, check one of the many Lotus books, such as Que's "Using 1-2-3."
The dividend payout ratio (column J) is the percentage of earnings actually paid out in dividends. It is defined as dividends per share divided by earnings per share.
Dividend Dividends Per Share (column E)
Payout = -------------------
Ratio Earnings Per Share (column D)
The earnings retention ratio (column K) is the percentage of earnings that are reinvested by the companies in the Dow Jones index. It is usually thought of as one minus the dividend payout ratio.
Earnings Earnings Retained Per Share
Retention = ---------------------------
Ratio Earnings Per Share
= 1 - Dividend Payout Ratio
= 1 - Column J
The dividend yield (column L) is the dividend (current cash flow) return as a percentage of the year-end value of the Dow Jones index.
Dividends Per Share (column E)
Div. Yield = -------------------
Year-End Value (DJIA) (column B)
Total return (column M) is the change in price plus dividends
per share divided by the closing price of the previous year.
(DJIA1 - DJIA0) + Dividend1
Total Return = ---------------------------
DJIA0
Where DJIA1 is this year's price (column B)
DJIA0 is the previous year's price (column B)
Dividend1 is the dividend
received during the year (column E)
The change in CPI in column F is the annual percentage change in the Consumer Price Index. This data is taken from the Federal Reserve Bulletin and is reported regularly in the media.
As you would expect, there is an inverse relationship between the rate of inflation and the price-earnings ratios. When inflation increases, so does investors' requited rate of return. This increase in the required rate of return means that investors are willing to pay less for a given level of earnings, since those earnings are inflated. The result is a lower P/E.
There are obviously many other such comparisons you can make. Examining the dividend yield on a regular basis, for example, would have indicated that a historically low yield prevailed in August, 1987. Other possibilities are limited by your imagination.
